﻿Imports System.Data.SqlClient
Imports System.IO

Public Class frmRFQ

    Dim FolderPath As String = FTPAddress + "Prospects Cases"

    Sub FillSentCases()
        Try
            Me.Cursor = Cursors.WaitCursor
           
            Dim cmd As New SqlCommand("Select SNo,ProsNo,SendUser,SendDate From ProspectCases Where Status='Under Process' and Branch=N'" & Branch & "' Order By SendDate ", cnn)
                                     
            Dim Reader As SqlDataReader
            Dim X As Integer = 1

            Clear()
            Me.ListPendingTrans.Items.Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While (Reader.Read)
                Dim Item As New ListViewItem
                Item.Text = Reader.Item("SNo")
                Item.SubItems.Add(X)
                Item.SubItems.Add(Reader.Item("ProsNo"))
                Item.SubItems.Add(Reader.Item("SendUser"))
                Item.SubItems.Add(CDate(Reader.Item("SendDate")).ToString("dd-MMM-yyyy HH:mm"))

                Me.ListPendingTrans.Items.Add(Item)
                X += 1
            End While
            cnn.Close()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub FillProspectsDocuments()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim Dir As New IO.DirectoryInfo(FolderPath & "\" & Me.ListPendingTrans.SelectedItems(0).SubItems(2).Text) 'sub is prospect no!
            Dim DirFiles As IO.FileInfo() = Dir.GetFiles()

            Me.GridFiles.Rows.Clear()

            'list the names of all files in the specified directory
            For Each Fi As FileInfo In DirFiles
                Me.GridFiles.Rows.Add(New String() {Fi.Name, Fi.CreationTime.ToString("yyyy/MM/dd hh:mm tt"), "Open", "Delete"})
            Next

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub Clear()
        Me.txtBusinessSize.Clear()
        Me.txtClassofBusiness.Clear()
        Me.txtExpiryDate.Clear()
        Me.txtInsurBroker.Clear()
        Me.txtNotes.Clear()
        Me.txtProspName.Clear()
        Me.txtTargetRate.Clear()
        Me.txtLineofBusiness.Clear()
        Me.txtProspID.Clear()
        Me.txtInsurComp.Clear()
        Me.GridFiles.Rows.Clear()

    End Sub

    Private Sub ListPendingTrans_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListPendingTrans.SelectedIndexChanged
        Try
            Clear()
            If Me.ListPendingTrans.SelectedItems.Count > 0 Then
                Me.Cursor = Cursors.WaitCursor

                Dim cmd As New SqlCommand("Select ProspectID,ProspectName,InsuranceCompany,InsuranceBroker," & _
                                          "ClassofBusiness,LineofBusiness,TargetRate,Notes,ExpiryDate,BusinessSize " & _
                                          "From ProspectCases Where SNo=" & Me.ListPendingTrans.SelectedItems(0).Text, cnn)
                Dim Reader As SqlDataReader

                cnn.Open()
                Reader = cmd.ExecuteReader
                While (Reader.Read)
                    Me.txtProspID.Text = Reader.Item("ProspectID")
                    Me.txtProspName.Text = Reader.Item("ProspectName")
                    Me.txtInsurComp.Text = Reader.Item("InsuranceCompany")
                    Me.txtInsurBroker.Text = Reader.Item("InsuranceBroker")
                    Me.txtClassofBusiness.Text = Reader.Item("ClassofBusiness")
                    Me.txtLineofBusiness.Text = Reader.Item("LineofBusiness")
                    Me.txtTargetRate.Text = Reader.Item("TargetRate")
                    Me.txtNotes.Text = Reader.Item("Notes")
                    If Reader.Item("ExpiryDate") Is DBNull.Value Then
                        Me.txtExpiryDate.Text = ""
                    Else
                        Me.txtExpiryDate.Text = CDate(Reader.Item("ExpiryDate")).ToString("yyyy/MM/dd")
                    End If
                    Me.txtBusinessSize.Text = Reader.Item("BusinessSize")

                End While
                Reader.Close()
                cnn.Close()

                FillProspectsDocuments()

                Me.ListPendingTrans.Focus()

                Me.Cursor = Cursors.Default
            End If
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub frmIssueRFQ_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.WindowState = FormWindowState.Maximized
        FillSentCases()
    End Sub

    Private Sub GridFiles_CellClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles GridFiles.CellClick
        If e.ColumnIndex = 2 Then
            Try
                Me.Cursor = Cursors.WaitCursor

                Dim FilePath As String = FolderPath + "\" + Me.ListPendingTrans.SelectedItems(0).SubItems(2).Text + "\" + Me.GridFiles.Rows(e.RowIndex).Cells(0).Value
                System.Diagnostics.Process.Start(FilePath)

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Update ProspectCases set Status=@Status where SNo=" & Me.ListPendingTrans.SelectedItems(0).Text, cnn)

            cnn.Open()
            'Add values
            cmd.Parameters.AddWithValue("@Status", "Received")
            cmd.ExecuteNonQuery()
            cnn.Close()

            Clear()

            FillSentCases()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub

    Private Sub ToolStripButton5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton5.Click
        Try
            If Me.ListPendingTrans.SelectedItems.Count > 0 Then
                Me.OpenFileDialog1.Filter = "All files (*.*)|*.*"
                If Me.OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then
                    Dim File As New FileInfo(Me.OpenFileDialog1.FileName)
                    File.CopyTo(FolderPath & "\" & Me.ListPendingTrans.SelectedItems(0).SubItems(2).Text & "\" & File.Name)

                    FillProspectsDocuments()
                End If
            End If
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub btnRefreshPendTrans_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRefreshPendTrans.Click
        FillSentCases()
    End Sub
End Class